﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using ThanhTu.GNCMedia.KQXS.DataAccess.DO;

namespace ThanhTu.GNCMedia.KQXS.DataAccess.DAL
{
    public class ResultLotteryDAL : BaseDAL
    {


        public ResultLotteryDAL() : base() { }

        public int add(ResultLotteryDO vResultLottery)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.SaveLotteryResult";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
            oraParam.Value = vResultLottery.ProvinceID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Awards_ID", OracleDbType.Int32);
            oraParam.Value = vResultLottery.KindOfawards;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Result_Time", OracleDbType.Date);
            oraParam.Value = vResultLottery.Opendate;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Result_Value", OracleDbType.Varchar2);
            oraParam.Value = vResultLottery.Result;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
            { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }

        public int update(ResultLotteryDO vResultLottery)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.UpdateLotteryResult";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
            oraParam.Value = vResultLottery.ProvinceID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Awards_ID", OracleDbType.Int32);
            oraParam.Value = vResultLottery.KindOfawards;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Result_Time", OracleDbType.Date);
            oraParam.Value = vResultLottery.Opendate;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Result_Value", OracleDbType.Varchar2);
            oraParam.Value = vResultLottery.Result;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
            { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }

        public int delete(string ProvinceID, DateTime INNGAY_MO)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.DeleteLotteryResult";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
            oraParam.Value = ProvinceID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Result_Time", OracleDbType.Date);
            oraParam.Value = INNGAY_MO;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
            { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }

        public int StatusLotteryResult(string ProvinceID, DateTime INNGAY_MO)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.StatusLotteryResult";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
            oraParam.Value = ProvinceID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Result_Time", OracleDbType.Date);
            oraParam.Value = INNGAY_MO;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
            { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }

        public List<ResultLotteryDO> SelectALL_KQ_QT()
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.GetAllResult_Admin";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;
            List<ResultLotteryDO> vKindOfAwardsDOCollection = null;
            DataRow dr = null;
            ResultLotteryDO vResultLotteryDO = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                    vKindOfAwardsDOCollection = new List<ResultLotteryDO>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dr = dt.Rows[i];
                        vResultLotteryDO = new ResultLotteryDO();

                        if (!Convert.IsDBNull(dr["PROVINCEID"]))
                        { vResultLotteryDO.ProvinceID = Convert.ToString(dr["PROVINCEID"]); }

                        if (!Convert.IsDBNull(dr["AWARDSID"]))
                        { vResultLotteryDO.KindOfawards = Convert.ToInt32(dr["AWARDSID"]); }


                        if (!Convert.IsDBNull(dr["OPENDATE"]))
                        { vResultLotteryDO.Opendate = Convert.ToDateTime(dr["OPENDATE"]); }

                        if (!Convert.IsDBNull(dr["RESULTVALUE"]))
                        { vResultLotteryDO.Result = Convert.ToString(dr["RESULTVALUE"]); }

                        vKindOfAwardsDOCollection.Add(vResultLotteryDO);
                        dr = null;
                        vResultLotteryDO = null;
                    }

                    dt.Dispose();
                    dt = null;
                }

                ds.Dispose();
                ds = null;
            }
            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return vKindOfAwardsDOCollection;
        }

        public List<ResultLotteryDO> Select_KQ_Tinh_QT(string ProvinceID, DateTime INNGAY_MO)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.Get_Result_Admin";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
            oraParam.Value = ProvinceID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Result_Time", OracleDbType.Date);
            oraParam.Value = INNGAY_MO;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;
            List<ResultLotteryDO> vKindOfAwardsDOCollection = null;
            DataRow dr = null;
            ResultLotteryDO vResultLotteryDO = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                    vKindOfAwardsDOCollection = new List<ResultLotteryDO>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dr = dt.Rows[i];
                        vResultLotteryDO = new ResultLotteryDO();

                        if (!Convert.IsDBNull(dr["PROVINCEID"]))
                        { vResultLotteryDO.ProvinceID = Convert.ToString(dr["PROVINCEID"]); }

                        if (!Convert.IsDBNull(dr["AWARDSID"]))
                        { vResultLotteryDO.KindOfawards = Convert.ToInt32(dr["AWARDSID"]); }


                        if (!Convert.IsDBNull(dr["OPENDATE"]))
                        { vResultLotteryDO.Opendate = Convert.ToDateTime(dr["OPENDATE"]); }

                        if (!Convert.IsDBNull(dr["RESULTVALUE"]))
                        { vResultLotteryDO.Result = Convert.ToString(dr["RESULTVALUE"]); }

                        vKindOfAwardsDOCollection.Add(vResultLotteryDO);
                        dr = null;
                        vResultLotteryDO = null;
                    }

                    dt.Dispose();
                    dt = null;
                }

                ds.Dispose();
                ds = null;
            }
            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return vKindOfAwardsDOCollection;
        }

        public DataTable SelectAll_KQ_QT_Page(int pageIndex, int pageSize, ref int rowCount)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.GetAll_Result_Admin_PAGE";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
            oraParam.Value = pageIndex;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
            oraParam.Value = pageSize;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];

                    if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                    { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }

        public DataTable SelectAll_KQ_User(int pageIndex, int pageSize, ref int rowCount)
        {
            OracleCommand oraComm = new OracleCommand();

            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.GetAll_Result_User";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
            oraParam.Value = pageIndex;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
            oraParam.Value = pageSize;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];

                    if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                    { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }

        public DataTable SelectAll_KQ_Tinh_User(string ProvinceID, int pageIndex, int pageSize, ref int rowCount)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.GetAll_Result_Province_User";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
            oraParam.Value = ProvinceID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
            oraParam.Value = pageIndex;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
            oraParam.Value = pageSize;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];

                    if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                    { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }

        public DataTable SelectAll_KQ_Region_User(string RegionID, int pageIndex, int pageSize, ref int rowCount)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.GetAll_Result_Region_User";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Region_ID", OracleDbType.Varchar2);
            oraParam.Value = RegionID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
            oraParam.Value = pageIndex;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
            oraParam.Value = pageSize;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];

                    if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                    { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }

        public DataTable SelectAll_KQ_Thongke(string thongke, string ProvinceID, DateTime Tu_ngay, DateTime Den_ngay)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.Statistic_Day";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Number", OracleDbType.Varchar2);
            oraParam.Value = thongke;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("PROVINCEID", OracleDbType.Varchar2);
            oraParam.Value = ProvinceID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_from_date", OracleDbType.Date);
            oraParam.Value = Tu_ngay;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_to_date", OracleDbType.Date);
            oraParam.Value = Den_ngay;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);


            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }

        public DataTable SelectAll_KQ_Thongke_Thang(string thongke, string ProvinceID, string Thang)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "LotteryResult_PKG.Statistic_Month";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Number", OracleDbType.Varchar2);
            oraParam.Value = thongke;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("PROVINCEID", OracleDbType.Varchar2);
            oraParam.Value = ProvinceID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Month", OracleDbType.Varchar2);
            oraParam.Value = Thang;
            oraComm.Parameters.Add(oraParam);


            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);


            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];


                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }
    }
}
